﻿Module TaskProgressDao

    Public Sub insert(ByRef taskProgress As TaskProgress)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "insert into " + taskProgress.getTableName + " "
        query &= "(task_progress_date, start_hour, end_hour, observations) "
        query &= "values(@task_progress_date, @start_hour, @end_hour, @observations)"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("task_progress_date", taskProgress.taskProgressDateProperty)
        command.Parameters.AddWithValue("start_hour", taskProgress.startHourProperty)
        command.Parameters.AddWithValue("end_hour", taskProgress.endHourProperty)
        command.Parameters.AddWithValue("observations", taskProgress.observationsProperty)

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub update(ByRef taskProgress As TaskProgress)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "update table " + taskProgress.getTableName + " "
        query &= "   set task_progress_date = @task_progress_date, "
        query &= "       start_hour  = @start_hour, "
        query &= "       end_hour    = @end_hour, "
        query &= "       observations= @observations "
        query &= " where id = @id "

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("id", taskProgress.idProperty)
        command.Parameters.AddWithValue("task_progress_date", taskProgress.taskProgressDateProperty)
        command.Parameters.AddWithValue("start_hour", taskProgress.startHourProperty)
        command.Parameters.AddWithValue("end_hour", taskProgress.endHourProperty)
        command.Parameters.AddWithValue("observations", taskProgress.observationsProperty)

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub


    Public Function searchDataByFilter(ByRef taskProgress As TaskProgress) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim resource As New Resource
        Dim role As New Role

        Dim query As String = ""

        query &= " SELECT tp.id id, "
        query &= " tp.task_progress_date task_progress_date, "
        query &= " tp.start_hour start_hour, "
        query &= " tp.end_hour end_hour, "
        query &= " tp.observations observations, "
        query &= " tp.percentage percentage, "
        query &= " tp.task_id task_id "
        query &= " FROM " & taskProgress.getTableName & " tp "
        query &= " WHERE (tp.id = :id OR 0 = :id)"
        query &= " AND (tp.task_id = :task_id OR 0 = :task_id)"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection
        command.Parameters.AddWithValue("id", taskProgress.idProperty)
        command.Parameters.AddWithValue("task_id", If(taskProgress.taskProperty Is Nothing, Nothing, taskProgress.taskProperty.idProperty))

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchData(ByRef taskProgress As TaskProgress) As ArrayList
        Dim table As New Data.DataTable
        Dim list As New ArrayList
        table = searchDataByFilter(taskProgress)
        list = convertTableToList(table)
        Return list
    End Function

    Private Function convertTableToTaskProgress(ByRef table As Data.DataTable) As TaskProgress
        Dim taskProgress As New TaskProgress()
        Dim task As New Task()

        taskProgress.idProperty = table.Rows(0)("id")
        taskProgress.taskProgressDateProperty = table.Rows(0)("task_progress_date")
        taskProgress.startHourProperty = table.Rows(0)("start_hour")
        taskProgress.endHourProperty = table.Rows(0)("end_hour")
        taskProgress.observationsProperty = table.Rows(0)("observations")
        taskProgress.percentageProperty = table.Rows(0)("percentage")
        task.idProperty = table.Rows(0)("task_id")
        taskProgress.taskProperty = task

        Return taskProgress
    End Function



    Private Function convertTableToList(ByRef table As Data.DataTable) As ArrayList
        Dim listTaskProgress As New ArrayList

        For Each row In table.Rows
            Dim taskProgress As New TaskProgress
            taskProgress.idProperty = row("id")
            taskProgress.taskProgressDateProperty = row("task_progress_date")
            taskProgress.startHourProperty = row("start_hour")
            taskProgress.endHourProperty = row("end_hour")
            taskProgress.observationsProperty = row("observations")
            taskProgress.percentageProperty = row("percentage")

            Dim task As New Task
            task.idProperty = row("task_id")
            taskProgress.taskProperty = task

            listTaskProgress.Add(taskProgress)
        Next

        Return listTaskProgress
    End Function
End Module
